package com.shujia.dal

import com.shujia.common.SparkTool
import org.apache.spark.sql.SparkSession

object DalCityTouristMskWideDay extends SparkTool{
  override def run(spark: SparkSession): Unit = {

    spark.sql(
      s"""
        |insert overwrite table dal.dal_city_tourist_msk_wide_d partition(day_id='$day_id')
        |select  /*+broadcast(t3,t4)*/
        |        t1.mdn
        |        ,t3.county_name as source_county_name
        |        ,t3.city_name as source_city_name
        |        ,t3.prov_name as source_prov_name
        |        ,t4.city_name
        |        ,t1.d_stay_time
        |        ,t1.d_max_distance as d_distance
        |        ,t2.gender
        |        ,t2.age
        |        ,t2.number_attr
        |        ,t2.trmnl_brand
        |        ,t2.trmnl_price
        |        ,t2.packg
        |        ,t2.conpot
        |from (
        |    select   mdn
        |            ,source_county_id
        |            ,d_city_id
        |            ,case   when d_stay_time/60/60 >= 0.5 and d_stay_time/60/60 < 1 then '[0.5,1)'
        |                    when d_stay_time/60/60 >= 1 and d_stay_time/60/60 < 3 then '[1,3)'
        |                    when d_stay_time/60/60 >= 3 and d_stay_time/60/60 < 6 then '[3,6)'
        |                    when d_stay_time/60/60 >= 6 and d_stay_time/60/60 < 12 then '[6,12)'
        |                    when d_stay_time/60/60 >= 12 and d_stay_time/60/60 < 24 then '[12,24)'
        |                    when d_stay_time/60/60 >= 24 and d_stay_time/60/60 < 48 then '[24,48)'
        |                    when d_stay_time/60/60 >= 48 and d_stay_time/60/60 < 72 then '[48,72)'
        |                    when d_stay_time/60/60 >= 72 and d_stay_time/60/60 < 96 then '[72,96)'
        |                    when d_stay_time/60/60 >= 96 then '[96,+∞)'
        |                    else '-'
        |            end as d_stay_time
        |            ,case   when d_max_distance/1000 >= 80 and d_max_distance/1000 < 120 then '80-120'
        |                    when d_max_distance/1000 >= 120 and d_max_distance/1000 < 150 then '120-150'
        |                    when d_max_distance/1000 >= 150 and d_max_distance/1000 < 200 then '150-200'
        |                    when d_max_distance/1000 >= 200 and d_max_distance/1000 < 300 then '200-300'
        |                    else '>300'
        |            end as d_max_distance
        |    from dws.dws_city_tourist_msk_d
        |    where day_id = '$day_id'
        |) t1 join (
        |    select  mdn
        |            ,case gender when '1' then '男'
        |                         when '2' then '女'
        |                         else '-'
        |            end as gender
        |            ,case   when age > 0 and age < 20 then '(0,20)'
        |                    when age >= 20 and age < 25 then '[20,25)'
        |                    when age >= 25 and age < 30 then '[25,30)'
        |                    when age >= 30 and age < 35 then '[30,35)'
        |                    when age >= 35 and age < 40 then '[35,40)'
        |                    when age >= 40 and age < 45 then '[40,45)'
        |                    when age >= 45 and age < 50 then '[45,50)'
        |                    when age >= 50 and age < 55 then '[50,55)'
        |                    when age >= 55 and age < 60 then '[55,60)'
        |                    when age >= 60 and age < 65 then '[60,65)'
        |                    when age >= 65 then '[65,+∞)'
        |                    else '-'
        |            end  as age
        |            ,number_attr
        |            ,trmnl_brand
        |            ,trmnl_price
        |            ,packg
        |            ,conpot
        |    from dim.dim_usertag_msk_d
        |    where day_id = '$day_id'
        |) t2 on t1.mdn = t2.mdn
        |join dim.dim_admincode t3
        |on t1.source_county_id = t3.county_id
        |join (
        |    select  distinct
        |            city_id
        |            ,city_name
        |    from dim.dim_admincode
        |) t4 on t1.d_city_id = t4.city_id
        |""".stripMargin)
  }
}
